"""
Case Type   : Ubtree索引增强
Case Name   : 校验ubtree空间回收_普通ustore表_满足回收条件
Create At   : 2025-11-6
Owner       : sungang14
Description :
    1、创建ustore表
    2、创建pcr索引
    3、批量插入测试数据
    4、查询索引文件路径
    5、pagehack工具解析出索引数据,查看页面存放数据情况
    6、删除数据
    7、pagehack工具解析出索引数据，查看页面数据已回收
Expect      :
    1、设置成功
    2、成功
    3、成功
    4、成功
    5、解析索引页面成功
History     :
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.table_name = f"t_ustore_ubtree_0069"
        self.index_name = f"i_ustore_ubtree_0069"
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"create table {self.table_name} (c1 int) with (storage_type=ustore);"
        self.prepare_data_02 = f"create index {self.index_name} on {self.table_name} (c1) with (index_type=pcr);"
        self.prepare_data_03 = f"insert into {self.table_name} (c1) select 1 from generate_series(1,382);"
        self.prepare_data_04 = f"insert into {self.table_name} (c1) select 2 from generate_series(1,382);"
        self.prepare_data_05 = f"insert into {self.table_name} (c1) select 3 from generate_series(1,382);"
        self.prepare_data_06 = f"SELECT pg_relation_filepath('{self.index_name}');"
        self.prepare_data_07 = f"delete from {self.table_name} where c1 =1; select pg_sleep(1);"
        self.prepare_data_08 = f"delete from {self.table_name} where c1 =3; select pg_sleep(1);"
        self.prepare_data_09 = f"delete from {self.table_name} where c1 =2; select pg_sleep(1);"
        self.prepare_data_10 = f"insert into {self.table_name} (c1) select 4 from generate_series(1,382);"

    def test_ustore_ubtree(self):
        text = '--step1:创建ustore表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step2:创建ubtree索引;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:批量插入测试数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertEqual(f"INSERT 0 382", data_cmd.strip(), '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertEqual(f"INSERT 0 382", data_cmd.strip(), '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertEqual(f"INSERT 0 382", data_cmd.strip(), '执行失败' + text)

        text = '--step4:查询索引文件路径;expect:成功--'
        logger.info(text)
        time.sleep(5)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn(f"1 row", data_cmd, '执行失败' + text)
        res_path = data_cmd.splitlines()[-2].strip()
        logger.info(res_path)

        text = '--step5:解析索引文件;expect:成功--'
        logger.info(text)
        ph_cmd = f"source {macro.DB_ENV_PATH}; cd $PGDATA; pagehack -f {res_path} -t ubtree_pcr_index"
        logger.info(ph_cmd)
        res = self.userNode.sh(ph_cmd).result()
        logger.info(res)
        self.assertNotIn(f'pd_checksum: 0x0, verify fail', res, '执行失败' + text)
        self.assertRegex(res, r"pd_checksum: 0x[0-9a-fA-F]+, verify success", '执行失败' + text)
        self.assertNotIn(self.constant.NO_FILE_MSG, res, '执行失败' + text)
        rpcount1 = res.splitlines()[-3].strip().split()[4].strip(',')
        logger.info(rpcount1)
        self.assertEqual(f"1151", rpcount1, '执行失败' + text)

        text = '--step6:删除数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 382", data_cmd.strip(), '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 382", data_cmd.strip(), '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_09)
        logger.info(data_cmd)
        self.assertIn(f"DELETE 382", data_cmd.strip(), '执行失败' + text)

        text = '--step7:解析索引文件;expect:成功--'
        logger.info(text)
        time.sleep(30)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_10)
        logger.info(data_cmd)
        time.sleep(30)
        ph_cmd = f"source {macro.DB_ENV_PATH}; cd $PGDATA; pagehack -f {res_path} -t ubtree_pcr_index"
        logger.info(ph_cmd)
        res = self.userNode.sh(ph_cmd).result()
        logger.info(res)
        self.assertNotIn(f'pd_checksum: 0x0, verify fail', res, '执行失败' + text)
        self.assertRegex(res, r"pd_checksum: 0x[0-9a-fA-F]+, verify success", '执行失败' + text)
        self.assertNotIn(self.constant.NO_FILE_MSG, res, '执行失败' + text)
        rpcount2 = res.splitlines()[-3].strip().split()[4].strip(',')
        logger.info(rpcount2)
        if f"1152" != rpcount2:
            self.assertGreater(int(rpcount1), int(rpcount2))
        else:
            logger.info(f"通过：{text}")

    def tearDown(self):
        text = '--step8:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
